package com.hu.biz.util;

import cn.hutool.core.io.file.FileNameUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.hu.common.domain.dto.ImportUserOrgDTO;
import com.hu.common.domain.dto.ImportUserRoleDTO;
import com.hu.common.domain.dto.PreEmpOaReportDTO;
import com.hu.common.domain.dto.RoleResDTO;
import com.hu.common.exception.BizException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author 00355760
 * @功能： 将excel表转成sql脚本，用于BASE_SYS_ROLE_DEPT表和BASE_SYS_USER_ROLE
 * @版本： V1
 * @版权： 信息技术有限公司（http://www.mychery.com/）
 * @作者： 磐石战队开发组  00355760
 * @日期： 2025/4/11 9:48
 */
@Slf4j
public class Excel2SqlUtil {

    private static final String BASE_SYS_ROLE_DEPT_SQL = "INSERT INTO BASE_SYS_ROLE_DEPT (ID, ROLE_ID, DEPT_ID, PSN_SCOPE_CODES) SELECT (SELECT LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 1E19)), 19, '0') AS random_number FROM dual) AS ID, ROLE_ID, '%s' AS DEPT_ID, '%s' AS PSN_SCOPE_CODES FROM BASE_SYS_ROLE WHERE ROLE_CODE = '%s' AND NOT EXISTS (SELECT 1 FROM BASE_SYS_ROLE_DEPT a inner  JOIN BASE_SYS_ROLE b ON a.ROLE_ID = b.ROLE_ID AND b.ROLE_CODE = '%s' AND a.DEPT_ID = '%s');";
    private static final String BASE_SYS_USER_ROLE_SQL = "INSERT INTO BASE_SYS_USER_ROLE (ID, USER_ID , ROLE_ID) SELECT (SELECT LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 1E19)), 19, '0') AS random_number FROM dual) AS ID, '%s' AS USER_ID, ROLE_ID FROM BASE_SYS_ROLE WHERE ROLE_CODE = '%s';";
    private static final String BASE_SYS_USER_ROLE_RES_SQL = "INSERT INTO BASE_SYS_ROLE VALUES ((SELECT LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 1E19)), 19, '0') AS random_number FROM dual), '%s', '', '%s', '%s', 1, 0, 'adminHrssc', SYSDATE, 'adminHrssc', SYSDATE);";
    private static final String BASE_SYS_OA_SQL = "INSERT INTO PRE_EMP_OA_REPORT (ID,OA_SN,OA_CANDIDATE_ID,OA_USER_CODE,OA_USER_NAME,OA_DEPT,OA_POST,OA_LEVEL,OA_EDU,OA_REQUEST_ID,IS_DELETED,CREATE_USER,CREATE_DATE,MODIFIED_USER,MODIFIED_DATE) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',0,'1',(SELECT cast(sysdate as TIMESTAMP) FROM dual),'1',(SELECT cast(sysdate as TIMESTAMP) FROM dual));";
    private static final String OUT_PUT_PATH = System.getProperty("user.home") + "/Desktop/sdc_output";

    public static void excel2RoleDeptSql(String filePath) throws IOException {
        StringBuilder sqlBuilder = new StringBuilder();
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // Excel中的原始数据
        List<Map<String, Object>> data = reader.read(0, 1, reader.getSheet().getLastRowNum());
        // 转成java对象
        AtomicInteger roleDeptSqlCount = new AtomicInteger(0);
        List<ImportUserOrgDTO> list = roleDeptData2List(data);
        list.forEach(dto -> {
            String format = String.format(BASE_SYS_ROLE_DEPT_SQL, dto.getRoleDeptId(), dto.getRolePsnScopeCodes(), dto.getRoleCode(), dto.getRoleCode(), dto.getRoleDeptId());
            sqlBuilder.append(format).append("\n");
            roleDeptSqlCount.incrementAndGet();
        });
        log.info("生成的角色组织关联sql总数：{}", roleDeptSqlCount.get());
        try {
            String prefix = FileNameUtil.getPrefix(filePath) + ".sql";
            Path dir = Paths.get(OUT_PUT_PATH);
            if (!Files.exists(dir)) {
                Files.createDirectories(dir);
            }
            Files.write(dir.resolve(prefix),
                    sqlBuilder.toString().getBytes(),
                    StandardOpenOption.CREATE);
        } catch (IOException e) {
            throw new BizException("SQL文件生成失败: " + e.getMessage());
        }

    }

    public static void excel2UserRoleSql(MultipartFile file) throws IOException {
        StringBuilder sqlBuilder = new StringBuilder();
        ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        // Excel中的原始数据
        List<Map<String, Object>> data = reader.read(0, 1, reader.getSheet().getLastRowNum());
        // 转成java对象
        AtomicInteger userRoleSqlCount = new AtomicInteger(0);
        List<ImportUserRoleDTO> list = userRoleData2List(data);
        list.forEach(dto -> {
            String format = String.format(BASE_SYS_USER_ROLE_SQL, dto.getRoleUser(), dto.getRoleCode());
            sqlBuilder.append(format).append("\n");
            userRoleSqlCount.incrementAndGet();
        });
        log.info("生成的角色用户关联sql总数：{}", userRoleSqlCount.get());
        try {
            String prefix = FileNameUtil.getPrefix(file.getOriginalFilename()) + ".sql";
            Path dir = Paths.get(OUT_PUT_PATH);
            if (!Files.exists(dir)) {
                Files.createDirectories(dir);
            }
            Files.write(dir.resolve(prefix),
                    sqlBuilder.toString().getBytes(),
                    StandardOpenOption.CREATE);
        } catch (IOException e) {
            throw new BizException("SQL文件生成失败: " + e.getMessage());
        }

    }

    /**
     * 新增表数据：BASE_SYS_ROLE_DEPT
     *
     * @param data
     * @return
     */
    private static List<ImportUserOrgDTO> roleDeptData2List(List<Map<String, Object>> data) {
        List<ImportUserOrgDTO> list = new ArrayList<>();
        for (Map<String, Object> map : data) {
            ImportUserOrgDTO dto = new ImportUserOrgDTO();
            String roleType = map.get("角色类型").toString();
            if ("审批角色".equals(roleType)) {
                roleType = "APPROVAL";
            } else if ("功能角色".equals(roleType)) {
                roleType = "SYSTEM";
            }
            dto.setRoleType(roleType);
            dto.setRoleCode(map.get("角色code").toString());
            dto.setRoleName(map.get("角色名称").toString());
            dto.setRoleDeptId(map.get("*角色勾选组织").toString());
            if (ObjectUtil.isNull(map.get("角色勾选人事范围（空即ALL）"))) {
                dto.setRolePsnScopeCodes(null);
            } else {
                dto.setRolePsnScopeCodes(map.get("角色勾选人事范围（空即ALL）").toString());
            }
            dto.setNote(map.get("更正记录").toString());
            list.add(dto);
        }
        return list;
    }

    /**
     * 新增表数据：BASE_SYS_USER_ROLE
     *
     * @param data
     * @return
     */
    private static List<ImportUserRoleDTO> userRoleData2List(List<Map<String, Object>> data) {
        List<ImportUserRoleDTO> list = new ArrayList<>();
        for (Map<String, Object> map : data) {
            ImportUserRoleDTO dto = new ImportUserRoleDTO();
            String roleType = map.get("角色类型").toString();
            if ("审批角色".equals(roleType)) {
                roleType = "APPROVAL";
            } else if ("功能角色".equals(roleType)) {
                roleType = "SYSTEM";
            }
            dto.setRoleType(roleType);
            dto.setRoleCode(map.get("角色code").toString());
            dto.setRoleName(map.get("角色名称").toString());
            dto.setRoleUser(map.get("角色分配人员").toString());
            dto.setNote(map.get("更正记录").toString());
            list.add(dto);
        }
        return list;
    }

    private static List<RoleResDTO> userRoleResData2List(List<Map<String, Object>> data) {
        List<RoleResDTO> list = new ArrayList<>();
        for (Map<String, Object> map : data) {
            RoleResDTO dto = new RoleResDTO();
            String roleType = map.get("角色类型").toString();
            if ("审批角色".equals(roleType)) {
                roleType = "APPROVAL";
            } else if ("功能角色".equals(roleType)) {
                roleType = "SYSTEM";
            }
            dto.setRoleType(roleType);
            dto.setRoleCode(map.get("角色code").toString());
            dto.setRoleName(map.get("角色名称").toString());
            list.add(dto);
        }
        return list;
    }

    public static void excel2RoleResSql(String filePath) throws IOException {
        StringBuilder sqlBuilder = new StringBuilder();
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // Excel中的原始数据
        List<Map<String, Object>> data = reader.read(0, 1, reader.getSheet().getLastRowNum());
        // 转成java对象
        AtomicInteger roleDeptSqlCount = new AtomicInteger(0);
        List<RoleResDTO> list = userRoleResData2List(data);
        list.forEach(dto -> {
            String format = String.format(BASE_SYS_USER_ROLE_RES_SQL, dto.getRoleName(), dto.getRoleCode(), dto.getRoleType());
            sqlBuilder.append(format).append("\n");
            roleDeptSqlCount.incrementAndGet();
        });
        log.info("生成的角色资源关联sql总数：{}", roleDeptSqlCount.get());
        try {
            String prefix = FileNameUtil.getPrefix(filePath) + ".sql";
            Path dir = Paths.get(OUT_PUT_PATH);
            if (!Files.exists(dir)) {
                Files.createDirectories(dir);
            }
            Files.write(dir.resolve(prefix),
                    sqlBuilder.toString().getBytes(),
                    StandardOpenOption.CREATE);
        } catch (IOException e) {
            throw new BizException("SQL文件生成失败: " + e.getMessage());
        }

    }

    private static List<PreEmpOaReportDTO> oaData2List(List<Map<String, Object>> data) {
        List<PreEmpOaReportDTO> list = new ArrayList<>();
        for (Map<String, Object> map : data) {
            PreEmpOaReportDTO dto = new PreEmpOaReportDTO();
            dto.setId(map.get("ID").toString());
            dto.setOaSn(map.get("OA_SN").toString());
            dto.setOaCandidateId(map.get("候选人编号").toString());
            dto.setOaUserCode(map.get("工号").toString());
            dto.setOaUserName(map.get("姓名").toString());
            dto.setOaDept(map.get("拟聘部门").toString());
            dto.setOaPost(map.get("拟聘岗位").toString());
            dto.setOaLevel(map.get("拟聘级别").toString());
            dto.setOaEdu(map.get("学历").toString());
            dto.setOaRequestId(map.get("业务ID").toString());
            list.add(dto);
        }
        return list;
    }

    public static void excel2OaSql(String filePath) throws IOException {
        StringBuilder sqlBuilder = new StringBuilder();
        ExcelReader reader = ExcelUtil.getReader(filePath);
        // Excel中的原始数据
        List<Map<String, Object>> data = reader.read(0, 1, reader.getSheet().getLastRowNum());
        // 转成java对象
        AtomicInteger roleDeptSqlCount = new AtomicInteger(0);
        List<PreEmpOaReportDTO> list = oaData2List(data);
        list.forEach(dto -> {
            String format = String.format(BASE_SYS_OA_SQL, dto.getId(), dto.getOaSn(), dto.getOaCandidateId(), dto.getOaUserCode(), dto.getOaUserName(), dto.getOaDept(), dto.getOaPost(), dto.getOaLevel(), dto.getOaEdu(), dto.getOaRequestId());
            sqlBuilder.append(format).append("\n");
            roleDeptSqlCount.incrementAndGet();
        });
        log.info("生成的OA-sql总数：{}", roleDeptSqlCount.get());
        try {
            String prefix = FileNameUtil.getPrefix(filePath) + ".sql";
            Path dir = Paths.get(OUT_PUT_PATH);
            if (!Files.exists(dir)) {
                Files.createDirectories(dir);
            }
            Files.write(dir.resolve(prefix),
                    sqlBuilder.toString().getBytes(),
                    StandardOpenOption.CREATE);
        } catch (IOException e) {
            throw new BizException("SQL文件生成失败: " + e.getMessage());
        }

    }

    public static void main(String[] args) throws IOException {
        excel2RoleResSql("D:\\BASE_SYS_ROLE_RES.xlsx");
        excel2RoleDeptSql("D:\\BASE_SYS_ROLE_DEPT.xlsx");
    }

}
